
require('lsqlite3')

local DB = class("DB")

function DB:ctor()
    self._db = nil
end

function DB:openWithFile(dbFile,readonly)
    if self._db then 
        self._db:close()
        self._db = nil
    end
    local flag = (readonly and 1 or 0)
    local db , code ,error  = sqlite3.open(dbFile,flag)
    if not db then
        printError("open sqlite3 db %s fail code:%d msg:%s",dbFile,code,error)
    else
        self._db = db
        local md5 = crypto.md5file(dbFile)
        if md5 then
            self._md5 =  string.sub(md5,1,7)
        end
    end
end

function DB:open()
    if self._db then self._db:close() end
    local db =  sqlite3.open_memory()
    self._db = db
    return db
end

function DB:close()
    if self._db then self._db:close() end
end

function DB:isopen()
    if self._db then 
        return self._db:isopen() 
    end
    return false
end

function DB:getSqliteDB()
    if self._db then 
        return self._db:getDB()
    end
    return nil
end

function DB:getMD5()
    return self._md5
end

function DB:trace(handle)
    local db = self._db
    if db then
        db:trace(function(ud,sql)
            if handle then
                handle(sql)
            end
        end)
    end
end

function DB:doSql(sql)
    return self._db:exec(sql)
end

function DB:prepare(sql)
    return self._db:prepare(sql)
end

function DB:getColnum(name)
    local vm = self:prepare("select * from "..name)
    if not vm then return 0 end
    return vm:columns()
end

function DB:getColNames(name)
    local vm = self:prepare("select * from "..name)
    if not vm then return {} end
    return vm:get_names()
end

function DB:isTableExist(name)
    return self:getColnum()>0
end

function DB:query(sql)
    local res = {}
    if self:isopen() then
        local code = self._db:exec(sql, function (ud, ncols, values, names)
            res[#res+1] = {}
            local row ={}
            for i=1,ncols do 
                local name = names[i]
                local value = values[i]
                row[name]=value
            end
            res[#res] = row
            return sqlite3.OK
        end)
        if code~=sqlite3.OK then
            print("===>fail query sql",sql,"code",code)
        end
    else
        print("DB:query fail because db not open ",sql)
    end
    return res
end

function DB:querySingle(sql)
    return self:query(sql)[1] or {}
end


--[[
    local res = DB:query("XXX",{id=100,name="will"})
    if res == nil then print("not find") end
    dump(res[1])
--]]

--[[
function DB:query(table,condition)
    local templet = "SELECT * FROM %s WHERE %s ;"
    local c = ""
    local cc = 0
    for k,v in pairs(condition) do
        if cc>0 then c = c.." AND " end
        if type(v)=="string" then
            c = k.."='"..v.."'"
        else
            c = k.."="..v
        end
        
        cc = cc + 1
    end
    local sql = string.format(templet,table,c)
    return self:query(sql)
end
]]


return DB